<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<link rel="STYLESHEET" href="lib.css" type='text/css' />
<link rel="SHORTCUT ICON" href="../icons/pyfav.png" type="image/png" />
<link rel='start' href='../index.html' title='Python documentation Index' />
<link rel="first" href="lib.html" title='Python library Reference' />
<link rel='contents' href='contents.html' title="Contents" />
<link rel='index' href='genindex.html' title='Index' />
<link rel='last' href='about.html' title='About this document...' />
<link rel='help' href='about.html' title='About this document...' />
<link rel="next" href="node348.html" />
<link rel="prev" href="node346.html" />
<link rel="parent" href="sqlite3-Types.html" />
<link rel="next" href="node348.html" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name='aesop' content='information' />
<title>13.13.4.3 Converting SQLite values to custom Python types</title>
</head>
<body>
<div class="navigation">
<div id='top-navigation-panel' xml:id='top-navigation-panel'>
<table align="center" width="100%" cellpadding="0" cellspacing="2">
<tr>
<td class='online-navigation'><a rel="prev" title="13.13.4.2 using adapters to"
  href="node346.html"><img src='../icons/previous.png'
  border='0' height='32'  alt='Previous Page' width='32' /></a></td>
<td class='online-navigation'><a rel="parent" title="13.13.4 sqlite and Python"
  href="sqlite3-Types.html"><img src='../icons/up.png'
  border='0' height='32'  alt='Up one Level' width='32' /></a></td>
<td class='online-navigation'><a rel="next" title="13.13.4.4 default adapters and"
  href="node348.html"><img src='../icons/next.png'
  border='0' height='32'  alt='Next Page' width='32' /></a></td>
<td align="center" width="100%">Python Library Reference</td>
<td class='online-navigation'><a rel="contents" title="Table of Contents"
  href="contents.html"><img src='../icons/contents.png'
  border='0' height='32'  alt='Contents' width='32' /></a></td>
<td class='online-navigation'><a href="modindex.html" title="Module Index"><img src='../icons/modules.png'
  border='0' height='32'  alt='Module Index' width='32' /></a></td>
<td class='online-navigation'><a rel="index" title="Index"
  href="genindex.html"><img src='../icons/index.png'
  border='0' height='32'  alt='Index' width='32' /></a></td>
</tr></table>
<div class='online-navigation'>
<b class="navlabel">Previous:</b>
<a class="sectref" rel="prev" href="node346.html">13.13.4.2 Using adapters to</a>
<b class="navlabel">Up:</b>
<a class="sectref" rel="parent" href="sqlite3-Types.html">13.13.4 SQLite and Python</a>
<b class="navlabel">Next:</b>
<a class="sectref" rel="next" href="node348.html">13.13.4.4 Default adapters and</a>
</div>
<hr /></div>
</div>
<!--End of Navigation Panel-->

<h3><a name="SECTION00151343000000000000000">
13.13.4.3 Converting SQLite values to custom Python types</a>
</h3>

<p>
Writing an adapter lets you send custom Python types to SQLite.
But to make it really useful we need to make the Python to SQLite to Python
roundtrip work.  

<p>
Enter converters.

<p>
Let's go back to the <tt class="class">Point</tt> class. We stored the x and y
coordinates separated via semicolons as strings in SQLite.

<p>
First, we'll define a converter function that accepts the string as a
parameter and constructs a <tt class="class">Point</tt> object from it.

<p>
<div class="note"><b class="label">Note:</b>
Converter functions <strong>always</strong> get called with a string, no matter
under which data type you sent the value to SQLite.
</div>

<p>
<div class="note"><b class="label">Note:</b>
Converter names are looked up in a case-sensitive manner.
</div>

<p>
<div class="verbatim"><pre>
    def convert_point(s):
        x, y = map(float, s.split(";"))
        return Point(x, y)
</pre></div>

<p>
Now you need to make the <tt class="module">sqlite3</tt> module know that what you select from the
database is actually a point. There are two ways of doing this:

<p>

<ul>
<li>Implicitly via the declared type
</li>
<li>Explicitly via the column name
</li>
</ul>

<p>
Both ways are described in ``Module Constants'', section&nbsp;<a href="sqlite3-Module-Contents.html#sqlite3-Module-Contents">13.13.1</a>, in
the entries for the constants <tt class="constant">PARSE_DECLTYPES</tt> and
<tt class="constant">PARSE_COLNAMES</tt>.

<p>
The following example illustrates both approaches.

<p>
<div class="verbatim">
<pre>import sqlite3

class Point(object):
    def __init__(self, x, y):
        self.x, self.y = x, y

    def __repr__(self):
        return "(%f;%f)" % (self.x, self.y)

def adapt_point(point):
    return "%f;%f" % (point.x, point.y)

def convert_point(s):
    x, y = map(float, s.split(";"))
    return Point(x, y)

# Register the adapter
sqlite3.register_adapter(Point, adapt_point)

# Register the converter
sqlite3.register_converter("point", convert_point)

p = Point(4.0, -3.2)

#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(p point)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("select p from test")
print "with declared types:", cur.fetchone()[0]
cur.close()
con.close()

#######################
# 1) Using column names
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_COLNAMES)
cur = con.cursor()
cur.execute("create table test(p)")

cur.execute("insert into test(p) values (?)", (p,))
cur.execute('select p as "p [point]" from test')
print "with column names:", cur.fetchone()[0]
cur.close()
con.close()
</pre>
<div class="footer">
<a href="converter_point.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/converter_point.py</span>).</a>
</div></div>

<p>

<div class="navigation">
<div class='online-navigation'>
<p></p><hr />
<table align="center" width="100%" cellpadding="0" cellspacing="2">
<tr>
<td class='online-navigation'><a rel="prev" title="13.13.4.2 using adapters to"
  href="node346.html"><img src='../icons/previous.png'
  border='0' height='32'  alt='Previous Page' width='32' /></a></td>
<td class='online-navigation'><a rel="parent" title="13.13.4 sqlite and Python"
  href="sqlite3-Types.html"><img src='../icons/up.png'
  border='0' height='32'  alt='Up one Level' width='32' /></a></td>
<td class='online-navigation'><a rel="next" title="13.13.4.4 default adapters and"
  href="node348.html"><img src='../icons/next.png'
  border='0' height='32'  alt='Next Page' width='32' /></a></td>
<td align="center" width="100%">Python Library Reference</td>
<td class='online-navigation'><a rel="contents" title="Table of Contents"
  href="contents.html"><img src='../icons/contents.png'
  border='0' height='32'  alt='Contents' width='32' /></a></td>
<td class='online-navigation'><a href="modindex.html" title="Module Index"><img src='../icons/modules.png'
  border='0' height='32'  alt='Module Index' width='32' /></a></td>
<td class='online-navigation'><a rel="index" title="Index"
  href="genindex.html"><img src='../icons/index.png'
  border='0' height='32'  alt='Index' width='32' /></a></td>
</tr></table>
<div class='online-navigation'>
<b class="navlabel">Previous:</b>
<a class="sectref" rel="prev" href="node346.html">13.13.4.2 Using adapters to</a>
<b class="navlabel">Up:</b>
<a class="sectref" rel="parent" href="sqlite3-Types.html">13.13.4 SQLite and Python</a>
<b class="navlabel">Next:</b>
<a class="sectref" rel="next" href="node348.html">13.13.4.4 Default adapters and</a>
</div>
</div>
<hr />
<span class="release-info">Release 2.5.1, documentation updated on 18th April, 2007.</span>
</div>
<!--End of Navigation Panel-->
<address>
See <i><a href="about.html">About this document...</a></i> for information on suggesting changes.
</address>
</body>
</html>
